package com.autoboot.service.impl;

import com.autoboot.config.AutoBootProperties;
import com.autoboot.domain.DynamicEntity;
import com.autoboot.domain.query.JoinTable;
import com.autoboot.domain.query.QueryMultiBo;
import com.autoboot.mapper.DynamicTableMapper;
import com.autoboot.service.DynamicTableService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 动态表服务实现类
 *
 * @author Maosw
 */
@Service
public class DynamicTableServiceImpl implements DynamicTableService {

    @Autowired
    private DynamicTableMapper dynamicTableMapper;

    @Autowired
    private AutoBootProperties properties;

    /**
     * 检查表是否被排除
     *
     * @param tableName 表名
     * @throws IllegalArgumentException 如果表被排除，则抛出异常
     */
    private void checkTableExcluded(String tableName) {
        if (properties.getDb().getExcludedTables() != null
                && properties.getDb().getExcludedTables().contains(tableName)) {
            throw new IllegalArgumentException("表 " + tableName + " 被禁止访问");
        }
    }

    @Override
    public List<Map<String, Object>> listFromTable(String tableName) {
        checkTableExcluded(tableName);
        QueryWrapper<DynamicEntity> wrapper = new QueryWrapper<>();
        return dynamicTableMapper.selectListByTable(tableName, wrapper);
    }

    @Override
    public List<Map<String, Object>> listByConditionFromTable(
            String tableName,
            Map<String, Object> conditions,
            List<Map<String, String>> likes,
            List<Map<String, Object>> gt,
            List<Map<String, Object>> ge,
            List<Map<String, Object>> lt,
            List<Map<String, Object>> le,
            List<Map<String, Object>> between,
            List<Map<String, String>> orders) {

        checkTableExcluded(tableName);
        QueryWrapper<DynamicEntity> wrapper = createComplexQueryWrapper(
                conditions, likes, gt, ge, lt, le, between, orders);
        return dynamicTableMapper.selectListByTable(tableName, wrapper);
    }

    @Override
    public Map<String, Object> getByIdFromTable(String tableName, String idField, Object id) {
        checkTableExcluded(tableName);
        QueryWrapper<DynamicEntity> wrapper = new QueryWrapper<>();
        wrapper.eq(idField, id);
        List<Map<String, Object>> list = dynamicTableMapper.selectListByTable(tableName, wrapper);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public Page<Map<String, Object>> pageFromTable(
            String tableName,
            long current,
            long size,
            Map<String, Object> conditions,
            List<Map<String, String>> likes,
            List<Map<String, Object>> gt,
            List<Map<String, Object>> ge,
            List<Map<String, Object>> lt,
            List<Map<String, Object>> le,
            List<Map<String, Object>> between,
            List<Map<String, String>> orders) {

        checkTableExcluded(tableName);
        Page<Map<String, Object>> page = new Page<>(current, size);
        QueryWrapper<DynamicEntity> wrapper = createComplexQueryWrapper(
                conditions, likes, gt, ge, lt, le, between, orders);
        IPage<Map<String, Object>> resultPage = dynamicTableMapper.selectPageByTable(page, tableName, wrapper);
        Page<Map<String, Object>> resultPageData = new Page<>(resultPage.getCurrent(), resultPage.getSize(), resultPage.getTotal());
        resultPageData.setRecords(resultPage.getRecords());
        return resultPageData;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean saveToTable(String tableName, Map<String, Object> data) {
        checkTableExcluded(tableName);
        if (properties.getDb().isAutoFillTime()) {
            LocalDateTime now = LocalDateTime.now();
            data.put(properties.getDb().getCreateTimeField(), now);
            data.put(properties.getDb().getUpdateTimeField(), now);
        }
        Map<String, Object> paramMap = new HashMap<>(data);
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(tableName).append(" (");
        StringBuilder values = new StringBuilder();
        values.append(" VALUES (");
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            sql.append(entry.getKey()).append(",");
            values.append("#{params.").append(entry.getKey()).append("},");
        }
        sql.setLength(sql.length() - 1);
        values.setLength(values.length() - 1);
        sql.append(")");
        values.append(")");
        String insertSql = sql + values.toString();
        return dynamicTableMapper.executeInsert(insertSql, paramMap) > 0;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean saveBatchToTable(String tableName, List<Map<String, Object>> dataList) {
        checkTableExcluded(tableName);
        if (dataList == null || dataList.isEmpty()) {
            return false;
        }
        boolean result = true;
        for (Map<String, Object> data : dataList) {
            if (!saveToTable(tableName, data)) {
                result = false;
            }
        }
        return result;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateToTable(String tableName, String idField, Map<String, Object> data) {
        checkTableExcluded(tableName);
        Object idValue = data.get(idField);
        if (idValue == null) {
            return false;
        }
        if (properties.getDb().isAutoFillTime()) {
            data.put(properties.getDb().getUpdateTimeField(), LocalDateTime.now());
        }
        Map<String, Object> paramMap = new HashMap<>(data);
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(tableName).append(" SET ");
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            if (!entry.getKey().equals(idField)) {
                sql.append(entry.getKey()).append(" = #{params.").append(entry.getKey()).append("},");
            }
        }
        sql.setLength(sql.length() - 1);
        sql.append(" WHERE ").append(idField).append(" = #{params.").append(idField).append("}");
        return dynamicTableMapper.executeUpdate(sql.toString(), paramMap) > 0;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean removeFromTable(String tableName, String idField, Object id) {
        checkTableExcluded(tableName);
        String sqlStatement = "DELETE FROM " + tableName + " WHERE " + idField + " = #{params.id}";
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("id", id);
        return dynamicTableMapper.executeDelete(sqlStatement, paramMap) > 0;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean removeByConditionFromTable(String tableName, Map<String, Object> conditions) {
        checkTableExcluded(tableName);
        if (conditions == null || conditions.isEmpty()) {
            return false;
        }
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(tableName).append(" WHERE ");
        int index = 0;
        for (Map.Entry<String, Object> entry : conditions.entrySet()) {
            if (index > 0) {
                sql.append(" AND ");
            }
            sql.append(entry.getKey()).append(" = #{params.").append(entry.getKey()).append("}");
            index++;
        }
        return dynamicTableMapper.executeDelete(sql.toString(), conditions) > 0;
    }

    @Override
    public List<Map<String, Object>> executeQuery(String sql, Map<String, Object> params) {
        // 检查SQL中是否包含被排除的表
        if (properties.getDb().getExcludedTables() != null) {
            for (String excludedTable : properties.getDb().getExcludedTables()) {
                if (sql.toLowerCase().contains(excludedTable.toLowerCase())) {
                    throw new IllegalArgumentException("SQL中包含被禁止访问的表: " + excludedTable);
                }
            }
        }
        return dynamicTableMapper.executeQuery(sql, params);
    }

    /**
     * 创建复杂条件的QueryWrapper
     */
    private QueryWrapper<DynamicEntity> createComplexQueryWrapper(
            Map<String, Object> conditions,
            List<Map<String, String>> likes,
            List<Map<String, Object>> gt,
            List<Map<String, Object>> ge,
            List<Map<String, Object>> lt,
            List<Map<String, Object>> le,
            List<Map<String, Object>> between,
            List<Map<String, String>> orders) {

        QueryWrapper<DynamicEntity> wrapper = new QueryWrapper<>();

        // 处理等值条件
        if (conditions != null && !conditions.isEmpty()) {
            conditions.forEach((key, value) -> {
                // 过滤掉分页相关的参数
                if (value != null && !"current".equals(key) && !"size".equals(key)
                        && !"pageIndex".equals(key) && !"pageSize".equals(key)) {
                    if (value instanceof String && StringUtils.isBlank((String) value)) {
                        // 跳过空字符串
                        return;
                    }
                    wrapper.eq(key, value);
                }
            });
        }

        // 处理模糊查询条件
        if (likes != null && !likes.isEmpty()) {
            likes.forEach(like -> {
                String field = like.get("field");
                String value = like.get("value");
                if (StringUtils.isNotBlank(field) && StringUtils.isNotBlank(value)) {
                    wrapper.like(field, value);
                }
            });
        }

        // 处理大于条件
        if (gt != null && !gt.isEmpty()) {
            gt.forEach(condition -> {
                String field = (String) condition.get("field");
                Object value = condition.get("value");
                if (StringUtils.isNotBlank(field) && value != null) {
                    wrapper.gt(field, value);
                }
            });
        }

        // 处理大于等于条件
        if (ge != null && !ge.isEmpty()) {
            ge.forEach(condition -> {
                String field = (String) condition.get("field");
                Object value = condition.get("value");
                if (StringUtils.isNotBlank(field) && value != null) {
                    wrapper.ge(field, value);
                }
            });
        }

        // 处理小于条件
        if (lt != null && !lt.isEmpty()) {
            lt.forEach(condition -> {
                String field = (String) condition.get("field");
                Object value = condition.get("value");
                if (StringUtils.isNotBlank(field) && value != null) {
                    wrapper.lt(field, value);
                }
            });
        }

        // 处理小于等于条件
        if (le != null && !le.isEmpty()) {
            le.forEach(condition -> {
                String field = (String) condition.get("field");
                Object value = condition.get("value");
                if (StringUtils.isNotBlank(field) && value != null) {
                    wrapper.le(field, value);
                }
            });
        }

        // 处理区间条件
        if (between != null && !between.isEmpty()) {
            between.forEach(condition -> {
                String field = (String) condition.get("field");
                Object start = condition.get("start");
                Object end = condition.get("end");
                if (StringUtils.isNotBlank(field) && start != null && end != null) {
                    wrapper.between(field, start, end);
                }
            });
        }

        // 处理排序条件
        if (orders != null && !orders.isEmpty()) {
            orders.forEach(order -> {
                String field = order.get("field");
                String orderType = order.get("order");
                if (StringUtils.isNotBlank(field) && StringUtils.isNotBlank(orderType)) {
                    if ("ASC".equalsIgnoreCase(orderType)) {
                        wrapper.orderByAsc(field);
                    } else if ("DESC".equalsIgnoreCase(orderType)) {
                        wrapper.orderByDesc(field);
                    }
                }
            });
        }

        return wrapper;
    }

    @Override
    public List<Map<String, Object>> getTableStructure(String tableName) {
        // 检查表是否被排除
        checkTableExcluded(tableName);

        // 构建查询表结构的SQL
        String sql = "SELECT " +
                "COLUMN_NAME as columnName, " +
                "DATA_TYPE as dataType, " +
                "CHARACTER_MAXIMUM_LENGTH as maxLength, " +
                "IS_NULLABLE as nullable, " +
                "COLUMN_DEFAULT as defaultValue, " +
                "COLUMN_COMMENT as comment, " +
                "COLUMN_KEY as columnKey " +
                "FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = DATABASE() " +
                "AND TABLE_NAME = #{params.tableName} " +
                "ORDER BY ORDINAL_POSITION";

        Map<String, Object> params = new HashMap<>();
        params.put("tableName", tableName);

        return dynamicTableMapper.executeQuery(sql, params);
    }

    @Override
    public Page<Map<String, Object>> pageMultiFromTable(QueryMultiBo queryMultiBo) {
        // 检查主表是否被排除
        checkTableExcluded(queryMultiBo.getMainTable());

        // 检查关联表是否被排除
        if (queryMultiBo.getJoinTables() != null) {
            for (JoinTable joinTable : queryMultiBo.getJoinTables()) {
                checkTableExcluded(joinTable.getTableName());
            }
        }

        // 构建SQL
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");

        // 处理查询字段
        if (queryMultiBo.getSelectFields() != null && !queryMultiBo.getSelectFields().isEmpty()) {
            sql.append(String.join(", ", queryMultiBo.getSelectFields()));
        } else {
            sql.append("*");
        }

        // 添加主表
        sql.append(" FROM ").append(queryMultiBo.getMainTable());

        // 处理关联表
        if (queryMultiBo.getJoinTables() != null) {
            for (JoinTable joinTable : queryMultiBo.getJoinTables()) {
                sql.append(" ").append(joinTable.getJoinType().getCode())
                        .append(" ").append(joinTable.getTableName())
                        .append(" ON ").append(joinTable.getJoinCondition());
            }
        }

        // 处理查询条件
        Map<String, Object> params = new HashMap<>();
        StringBuilder whereClause = new StringBuilder();

        // 处理等值条件
        if (queryMultiBo.getConditions() != null && !queryMultiBo.getConditions().isEmpty()) {
            for (Map.Entry<String, Object> entry : queryMultiBo.getConditions().entrySet()) {
                if (whereClause.length() > 0) {
                    whereClause.append(" AND ");
                }
                whereClause.append(entry.getKey()).append(" = #{params.").append(entry.getKey()).append("}");
                params.put(entry.getKey(), entry.getValue());
            }
        }

        // 处理模糊查询条件
        if (queryMultiBo.getLikes() != null && !queryMultiBo.getLikes().isEmpty()) {
            for (Map.Entry<String, String> entry : queryMultiBo.getLikes().entrySet()) {
                String field = entry.getKey();
                String value = entry.getValue();
                if (StringUtils.isNotBlank(field) && StringUtils.isNotBlank(value)) {
                    if (whereClause.length() > 0) {
                        whereClause.append(" AND ");
                    }
                    whereClause.append(field).append(" LIKE #{params.like_").append(field).append("}");
                    params.put("like_" + field, "%" + value + "%");
                }
            }
        }

        // 处理比较条件
        processComparisonConditions(queryMultiBo.getGt(), ">", whereClause, params);
        processComparisonConditions(queryMultiBo.getGe(), ">=", whereClause, params);
        processComparisonConditions(queryMultiBo.getLt(), "<", whereClause, params);
        processComparisonConditions(queryMultiBo.getLe(), "<=", whereClause, params);

        // 处理区间条件
        if (queryMultiBo.getBetween() != null && !queryMultiBo.getBetween().isEmpty()) {
            for (Map.Entry<String, List<Object>> entry : queryMultiBo.getBetween().entrySet()) {
                String field = entry.getKey();
                List<Object> values = entry.getValue();
                if (StringUtils.isNotBlank(field) && values != null && values.size() >= 2) {
                    if (whereClause.length() > 0) {
                        whereClause.append(" AND ");
                    }
                    whereClause.append(field).append(" BETWEEN #{params.").append(field).append("Start} AND #{params.").append(field).append("End}");
                    params.put(field + "Start", values.get(0));
                    params.put(field + "End", values.get(1));
                }
            }
        }

        // 添加WHERE子句
        if (whereClause.length() > 0) {
            sql.append(" WHERE ").append(whereClause);
        }

        // 处理排序
        if (queryMultiBo.getOrders() != null && !queryMultiBo.getOrders().isEmpty()) {
            sql.append(" ORDER BY ");
            boolean firstOrder = true;
            for (Map.Entry<String, String> entry : queryMultiBo.getOrders().entrySet()) {
                if (!firstOrder) {
                    sql.append(", ");
                }
                sql.append(entry.getKey()).append(" ").append(entry.getValue());
                firstOrder = false;
            }
        }

        // 执行查询
        Page<Map<String, Object>> page = new Page<>(queryMultiBo.getCurrent(), queryMultiBo.getSize());
        IPage<Map<String, Object>> records = dynamicTableMapper.executePage(sql.toString(), params,page);

        // 设置分页结果
        page.setRecords(records.getRecords());
        page.setTotal(records.getTotal()); // 注意：这里需要根据实际情况计算总记录数

        return page;
    }

    /**
     * 处理比较条件
     */
    private void processComparisonConditions(Map<String, Object> conditions, String operator,
                                             StringBuilder whereClause, Map<String, Object> params) {
        if (conditions != null && !conditions.isEmpty()) {
            for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                String field = entry.getKey();
                Object value = entry.getValue();
                if (StringUtils.isNotBlank(field) && value != null) {
                    if (whereClause.length() > 0) {
                        whereClause.append(" AND ");
                    }
                    whereClause.append(field).append(" ").append(operator).append(" #{params.").append(operator.replace(">", "gt").replace("<", "lt").replace("=", "e")).append("_").append(field).append("}");
                    params.put(operator.replace(">", "gt").replace("<", "lt").replace("=", "e") + "_" + field, value);
                }
            }
        }
    }
}
